--------------------------------------------------------
-- Copyright 2008-2009 Sanjay Dwivedi.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--------------------------------------------------------
--------------------------------------------------------
-- TRIGGERS FOR SEQUENCE NUMBERS
--------------------------------------------------------

--------------------------------------------------------
-- TRIGGER ON CUSTOMER FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_CUSTOMER
	BEFORE INSERT 
	ON CUSTOMER
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  CUSTOMER_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM CUSTOMER;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT CUSTOMER_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_CUSTOMER;

/

--------------------------------------------------------
-- TRIGGER ON ASSET FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_ASSET
	BEFORE INSERT 
	ON ASSET
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  ASSET_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM ASSET;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT ASSET_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_ASSET;

/

--------------------------------------------------------
-- TRIGGER ON EVENT FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_EVENT
	BEFORE INSERT 
	ON EVENT
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  EVENT_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM EVENT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT EVENT_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_EVENT;

/

--------------------------------------------------------
-- TRIGGER ON CONTACT FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_CONTACT
	BEFORE INSERT 
	ON CONTACT
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  CONTACT_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM CONTACT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT CONTACT_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_CONTACT;

/

--------------------------------------------------------
-- TRIGGER ON ACCOUNT FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_ACCOUNT
	BEFORE INSERT 
	ON ACCOUNT
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  ACCOUNT_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM ACCOUNT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT ACCOUNT_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_ACCOUNT;

/

--------------------------------------------------------
-- TRIGGER ON SUB_ACCOUNT FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_SUB_ACCOUNT
	BEFORE INSERT 
	ON SUB_ACCOUNT
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  SUB_ACCOUNT_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM SUB_ACCOUNT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT SUB_ACCOUNT_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_SUB_ACCOUNT;

/

--------------------------------------------------------
-- TRIGGER ON PORTFOLIO FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_PORTFOLIO
	BEFORE INSERT 
	ON PORTFOLIO
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  PORTFOLIO_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM PORTFOLIO;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT PORTFOLIO_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_PORTFOLIO;

/
--------------------------------------------------------
-- TRIGGER ON POSITION FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_POSITION
	BEFORE INSERT 
	ON POSITION
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  POSITION_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM POSITION;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT POSITION_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_POSITION;

/
--------------------------------------------------------
-- TRIGGER ON POSITION_TRANSACTION FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_POSITION_TRANSACTION
	BEFORE INSERT 
	ON POSITION_TRANSACTION
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  POSITION_TRANSACTION_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM CONTACT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT POSITION_TRANSACTION_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_POSITION_TRANSACTION;

/
--------------------------------------------------------
-- TRIGGER ON POSITION_SNAPSHOT FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_POSITION_SNAPSHOT
	BEFORE INSERT 
	ON POSITION_SNAPSHOT
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
v_incval NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  POSITION_SNAPSHOT_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT max(ID) INTO v_newVal FROM POSITION_SNAPSHOT;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT POSITION_SNAPSHOT_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_POSITION_SNAPSHOT;

/
/*******************************************************
* TABLES WITH RELATIVELY SMALLER NUMBER OF RECORDS
********************************************************/
--------------------------------------------------------
-- TRIGGER ON XXX FOR GENERATING PRIMARY KEY IDs
--------------------------------------------------------

CREATE OR REPLACE TRIGGER TGR_XXX
	BEFORE INSERT 
	ON XXX
	FOR EACH ROW
DECLARE 
v_newVal NUMBER := 0;
BEGIN
  IF :new.ID IS NULL THEN
    SELECT  ID_GENERATOR_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
   -- assign the value from the sequence to emulate the identity column
   :new.ID := v_newVal;
  END IF;
  
  EXCEPTION
		WHEN OTHERS THEN
			RAISE;

END TGR_XXX;

/

/*******************************************************
* REFERENCE DATA
********************************************************/
